package core;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;

import xml.MyDom4j;

import dataBase.DbConnection;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class Test {

	private static Logger logger = Logger.getLogger(Test.class);

	public static void GetDbStruct(String tbName) throws DocumentException,
			SQLException, Exception {
		logger.debug("GetStruct Beginning");

		List<String> tbList = new ArrayList<String>();
		tbList.add(tbName);
		DbConnection dbCon = new DbConnection("MSSQL", "sa", "123456");
		try {
			dbCon.Connection();

			tbList = DbInfo.JudgeTableList(tbList, dbCon);

			Document doc = DbInfo.GetStruct(dbCon, tbList);

			MyDom4j.CreatXML(doc, "test", "DataBaseStruct.xml");

		} finally {
			dbCon.CloseConnection();
			logger.debug("GetStruct Finished");
		}
	}

	public static void GetData(String tbName) throws DocumentException,
			SQLException, Exception {
		logger.debug("DataObtain Beginning");

		List<String> tbList = new ArrayList<String>();
		tbList.add(tbName);
		DbConnection dbCon = new DbConnection("MSSQL", "sa", "123456");
		try {
			dbCon.Connection();
			Document doc = DataObtain.ObtainData(dbCon, "test"
					+ File.separatorChar + "DataBaseStruct.xml", tbList);
			MyDom4j.CreatXML(doc, "test", "DataBaseData.xml");
		} finally {
			dbCon.CloseConnection();
			logger.debug("DataObtain Finished");
		}
	}

	public static boolean CreateStruct() throws DocumentException, Exception {
		logger.debug("CreateStruct Beginning");
		String fileName = "test/DataBaseStruct.xml";

		File file = new File(fileName);
		if (!file.exists()) {
			System.out
					.println("StructFile does't exist CreateStruct can't Begin");
			return false;
		}
		Document doc = MyDom4j.Read(fileName);
		DbConnection dbCon = new DbConnection("MYSQL", "root", "root");
		List<String> sqllist = StructTransfer.TransferStruct(doc, "MYSQL");

		try {
			dbCon.Connection();
			dbCon.ExcuteTransaction(sqllist);
		} catch (SQLException e) {
			throw e;
		} finally {
			dbCon.CloseConnection();
			logger.debug("CreateStruct Finished");
		}
		return true;
	}

	public static void StoreData() throws DocumentException, Exception {

		File file = new File("test/DataBaseData.xml");
		if (!file.exists()) {
			System.out.println("DataFile does't exist StoreData can't Begin");
			return;
		}
		DbConnection dbCon = new DbConnection("MYSQL", "root", "root");
		logger.debug("DataStore Beginning");
		//
		try {
			dbCon.Connection();
			DataStore.StoreData(dbCon, "test/DataBaseData.xml");
			// }
		} finally {
			dbCon.CloseConnection();
			logger.debug("DataStore Finished");
		}
	}

	public static void InitialData() throws DocumentException, Exception {

		int[] pkList = { 1, 2, 3, 4 };
		List<String> fileList = new ArrayList<String>();
		fileList.add("p1.jpg");
		fileList.add("p2.jpg");
		fileList.add("p3.jpg");
		fileList.add("p4.jpg");
		DbConnection dbCon = new DbConnection("MSSQL", "sa", "123456");
		try {
			dbCon.Connection();
			String sql = "INSERT INTO IMAGE(ID,IMAGE) VALUES(?,?)";
			PreparedStatement ps = dbCon.GetPreparedStatement(sql);
			;
			// List<FileInputStream> fisList =new ArrayList<FileInputStream>();
			for (int i = 0; i < fileList.size(); i++) {
				FileInputStream fis = new FileInputStream(
						"C:\\Users\\Administrator\\workspace\\Graduate\\image\\"
								+ fileList.get(i));
				ps.setInt(1, pkList[i]);
				ps.setBinaryStream(2, fis);
				ps.addBatch();
			}
			ps.executeBatch();
		} finally {
			dbCon.CloseConnection();
		}
	}

	public static void ShowData() throws DocumentException, Exception {
		int[] pkList = { 1, 2, 3, 4 };
		List<String> fileList = new ArrayList<String>();
		fileList.add("p1.jpg");
		fileList.add("p2.jpg");
		fileList.add("p3.jpg");
		fileList.add("p4.jpg");
		DbConnection dbCon = new DbConnection("MYSQL", "root", "root");
		try {
			dbCon.Connection();
			String sql = "SELECT IMAGE FROM IMAGE WHERE ID=?";
			PreparedStatement ps = dbCon.GetPreparedStatement(sql);

			for (int i = 0; i < pkList.length; i++) {
				ps.setInt(1, pkList[i]);
				ResultSet rs = ps.executeQuery();
				if (rs == null) {
					System.out.println("null");
				}
				while (rs.next()) {

					Blob blob = rs.getBlob(1);
					BufferedInputStream bis = new BufferedInputStream(
							blob.getBinaryStream());
					BufferedOutputStream bos = new BufferedOutputStream(
							new FileOutputStream("d:\\" + fileList.get(i)));
					byte[] buffer = new byte[3072];
					int count = -1;
					while ((count = bis.read(buffer, 0, 3072)) != -1) {
						bos.write(buffer, 0, count);
					}
					bos.flush();
					bos.close();
				}
			}

		} finally {
			dbCon.CloseConnection();
		}
	}

	public static void XmlTransfer() throws DocumentException, SQLException,
			Exception {
		PropertyConfigurator
				.configure("C:\\Users\\Administrator\\workspace\\Graduate\\log\\log4j.properties");
		// logger.debug("debug message beginning");
//		 GetDbStruct( "Test_Table");
//		 GetData( "Test_Table");
//		 GetDbStruct("image");
//		 GetData("image");
		 CreateStruct();
		 StoreData();
		// logger.debug("debug message is over");
	}

	public static void main(String[] args) throws DocumentException,
			SQLException, Exception {
	ShowData();
//		XmlTransfer();
	}
}

// public static void test2(String dbtype,String user,String pwd) throws
// DocumentException, Exception {
// String fileName = "XXYDATA_test.XML";
// List<List<String>> sqlList = DataStore.StoreData(fileName);
//
// DbConnection dbCon = new DbConnection(dbtype, user, pwd);
// try {
// dbCon.Connection();
// for(int i=0;i<sqlList.size();i++)
// {
// int[] num;
// num=dbCon.ExcuteTransaction(sqlList.get(i));
// for(int j=0;j<num.length;j++)
// {
// System.out.print(num[j]+"\t");
// }
// System.out.println();
// }
//
// } catch (SQLException e) {
// throw e;
// }finally{
// dbCon.CloseConnection();
// }
// }
//
// public static void test1(String dbtype,String user,String pwd) throws
// DocumentException, Exception {
// String fileName="XXY_test.XML";
// Document doc=MyDom4j.Read(fileName);
// DbConnection dbCon = new DbConnection(dbtype, user, pwd);
// List<String> sqllist=StructTransfer.TransferStruct(doc,dbtype);
// int[] num;
// try {
// dbCon.Connection();
// num=dbCon.ExcuteTransaction(sqllist);
// for(int i=0;i<num.length;i++)
// {
// System.out.print(num[i]+"\t");
// }
// System.out.println();
// } catch (SQLException e) {
// throw e;
// }finally{
// dbCon.CloseConnection();
// }
// }
//
// public static void testMSSQL() throws DocumentException, Exception
// {
// String dbtype="MSSQL";
// String user="sa";
// String pwd="123456";
// test1(dbtype,user,pwd);
// test2(dbtype,user,pwd);
// }
//
// public static void testMYSQL() throws DocumentException, Exception{
// String dbtype="MYSQL";
// String user="root";
// String pwd="root";
// test1(dbtype,user,pwd);
// test2(dbtype,user,pwd);
// }
//
// public static void testORACLE() throws DocumentException, Exception{
// String dbtype="ORACLE";
// String user="system";
// String pwd="root";
// test1(dbtype,user,pwd);
// System.out.println("test1 passed");
// test2(dbtype,user,pwd);
// }

